package com.liyi.dao;

import java.math.BigDecimal;
import java.util.List;

import javax.inject.Inject;

import org.springframework.stereotype.Repository;

import com.zero.basic.dao.DefaultBaseDao;
import com.zero.basic.dao.SqlListBaseDao;
import com.zero.basic.dao.SqlQueryBaseDao;
import com.zero.basic.dao.SqlUpdateBaseDao;
import com.zero.basic.exception.SmsDaoException;
import com.zero.mms.model.EmpSalary;

/* date都是String，month都是Integer (唯独获取工资值用string) */

@Repository("empSalaryDaoLiyi")
public class EmpSalaryDaoImpl extends DefaultBaseDao<EmpSalary> implements EmpSalaryDao {

	@Inject
	private SqlListBaseDao<?> sqlListBaseDao;
	@Inject
	private SqlUpdateBaseDao<?> sqlUpdateBaseDao;
	@Inject
	private SqlQueryBaseDao<?> sqlQueryBaseDao;
	
	/* 默认当前年下，获取某 [员工] 在某 [月] 的所有 [工资项] 的键值数据 */
	@Override
	public List<EmpSalary> getESsByEmpIdAndDate(Integer empId, String date, String state) throws SmsDaoException {
		try {
			// 声明SQL语句
			String sql = "select e_s.* from t_emp_salary e_s"
					+ " where e_s.emp_id = " + empId 
					+ " and e_s.emp_salary_date = '" + date + "'"
					+ " and e_s.salary_item_id in ("
					+ "select salary_item_id from t_salary_item"
					+ " where salary_show = '是')";
			if ("已发".equals(state)) {
				sql += " and (e_s.emp_salary_state <> '已发' or e_s.emp_salary_state is null)";
			}
			// 获取查询结果
			List<EmpSalary> empSalaries = sqlListBaseDao.listBySql(sql, null, EmpSalary.class, true, true);
			// 返回查询结果
			return empSalaries;
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	
	/* 计算某 [员工] 在某 [月] 的所有 [计算工资项] 的值并写入数据库 */
	@Override
	public void updateNumById(Integer empSalaryNum, Integer empSalaryId) throws SmsDaoException {
		try {
			// 声明SQL语句
			String sql = "update t_emp_salary e_s set e_s.emp_salary_num = " + empSalaryNum +" where e_s.emp_salary_id = " + empSalaryId;
			// 执行更新操作
			sqlUpdateBaseDao.updateBySql(sql);
		} catch (Exception e) {
			e.printStackTrace();
			throw new SmsDaoException();
		}
	}
	
	/* 设置某 [员工] 的 在某 [月] 的 [工资项] 状态为 发放 */
	@Override
	public void updateStateById(String state, Integer empSalaryId) throws SmsDaoException {
		try {
			// 声明SQL语句
			String sql = "update t_emp_salary e_s set e_s.emp_salary_state = '" + state + "' where e_s.emp_salary_id = " + empSalaryId;
			// 执行更新操作
			sqlUpdateBaseDao.updateBySql(sql);
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	
	/* 获取某 [员工] 在某 [月] 的 某[工资项] 的 [工资值] */
	@Override
	public Integer getSalaryNum(Integer empId, String date, Integer salaryItemId) throws SmsDaoException {
		try {
			// 声明SQL语句
			String sql = "select e_s.emp_salary_num from t_emp_salary e_s where e_s.emp_id = " + empId + " and e_s.emp_salary_date = '" + date + "' and e_s.salary_item_id = " + salaryItemId;
			// 获取查询结果
			String salaryNum = (String) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			// 返回查询结果
			if (salaryNum == null || "".equals(salaryNum)) {
				salaryNum = "0";
			}
			return Integer.parseInt(salaryNum);
		} catch (Exception e) {
			e.printStackTrace();
			throw new SmsDaoException();
		}
	}

	/* 获取某 [年度] 某 [月度] 公司的 总工资 */
	@Override
	public Integer getTotalSalary(Integer year, Integer month) throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-";
			if (month != null) {
				if (month < 10) {
					date += "0" + month;
				}
				else {
					date += month;
				}
			}
			else {
				date += "%";
			}
			// 声明SQL语句
			String sql = "select SUM(e_s.emp_salary_num) from t_emp_salary e_s where e_s.emp_salary_date like '" + date + "' and e_s.emp_salary_state = '已发' and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_type = '实发')";
			// 获取查询结果
			Double totalSalary = (Double) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (totalSalary == null) {
				totalSalary = 0.0;
			}
			// 返回查询结果
			return totalSalary.intValue();
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	/* 获取某 [年度] 某 [月度] 公司的 平均工资  */
	@Override
	public Integer getAvgSalary(Integer year, Integer month) throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-";
			if (month != null) {
				if (month < 10) {
					date += "0" + month;
				}
				else {
					date += month;
				}
			}
			else {
				date += "%";
			}
			// 声明SQL语句
			String sql = "select cast(avg(e_s.emp_salary_num) as decimal(12,0)) from t_emp_salary e_s where e_s.emp_salary_date like '" + date + "' and e_s.emp_salary_state = '已发' and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_type = '实发')";
			// 获取查询结果
			BigDecimal avgSalary = (BigDecimal) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (avgSalary == null) {
				avgSalary = new BigDecimal(0);
			}
			// 返回查询结果
			return avgSalary.intValue();
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	/* 获取某 [年度] 某 [月度] 公司的 最低工资  */
	@Override
	public Integer getMinSalary(Integer year, Integer month) throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-";
			if (month != null) {
				if (month < 10) {
					date += "0" + month;
				}
				else {
					date += month;
				}
			}
			else {
				date += "%";
			}
			// 声明SQL语句
			String sql = "select min(e_s.emp_salary_num) from t_emp_salary e_s where e_s.emp_salary_date like '" + date + "' and e_s.emp_salary_state = '已发' and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_type = '实发')";
			// 获取查询结果
			String minSalary = (String) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (minSalary == null) {
				minSalary = "0";
			}
			// 返回查询结果
			return Integer.parseInt(minSalary);
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	/* 获取某 [年度] 某 [月度] 公司的 最高工资  */
	@Override
	public Integer getMaxSalary(Integer year, Integer month) throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-";
			if (month != null) {
				if (month < 10) {
					date += "0" + month;
				}
				else {
					date += month;
				}
			}
			else {
				date += "%";
			}
			// 声明SQL语句
			String sql = "select max(e_s.emp_salary_num) from t_emp_salary e_s where e_s.emp_salary_date like '" + date + "' and e_s.emp_salary_state = '已发' and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_type = '实发')";
			// 获取查询结果
			String maxSalary = (String) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (maxSalary == null) {
				maxSalary = "0";
			}
			// 返回查询结果
			return Integer.parseInt(maxSalary);
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}

	/* 获取某 [年度] 某 [月度] 某[部门](含子部门)的 总基本工资 */
	public Integer getDepTotalBaseSalary(Integer year, Integer month, String allDeps) throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-";
			if (month != null) {
				if (month < 10) {
					date += "0" + month;
				}
				else {
					date += month;
				}
			}
			else {
				date += "%";
			}
			// 声明SQL语句
			String sql = "select SUM(e_s.emp_salary_num) from t_emp_salary e_s where e_s.emp_salary_date like '" + date 
					+ "' and e_s.emp_salary_state = '已发'"
					+ " and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_name = '基本工资')"
					+ " and e_s.emp_id in (select emp_id from t_emp where dep_id in (" + allDeps 
					+ "))";
			// 获取查询结果
			Double depTotalBaseSalary = (Double) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (depTotalBaseSalary == null) {
				depTotalBaseSalary = 0.0;
			}
			// 返回查询结果
			return depTotalBaseSalary.intValue();
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	/* 获取某 [年度] 某 [月度] 某[部门](含子部门)的 平均基本工资 */
	public Integer getDepAvgBaseSalary(Integer year, Integer month, String allDeps) throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-";
			if (month != null) {
				if (month < 10) {
					date += "0" + month;
				}
				else {
					date += month;
				}
			}
			else {
				date += "%";
			}
			// 声明SQL语句
			String sql = "select cast(avg(e_s.emp_salary_num) as decimal(12,0)) from t_emp_salary e_s where e_s.emp_salary_date like '" + date 
					+ "' and e_s.emp_salary_state = '已发'"
					+ " and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_name = '基本工资')"
					+ " and e_s.emp_id in (select emp_id from t_emp where dep_id in (" + allDeps 
					+ "))";
			// 获取查询结果
			BigDecimal depAvgBaseSalary = (BigDecimal) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (depAvgBaseSalary == null) {
				depAvgBaseSalary = new BigDecimal(0);
			}
			// 返回查询结果
			return depAvgBaseSalary.intValue();
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	/* 获取某 [年度] 某 [月度] 某[部门](含子部门)的 最低基本工资 */
	public Integer getDepMinBaseSalary(Integer year, Integer month, String allDeps) throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-";
			if (month != null) {
				if (month < 10) {
					date += "0" + month;
				}
				else {
					date += month;
				}
			}
			else {
				date += "%";
			}
			// 声明SQL语句
			String sql = "select min(e_s.emp_salary_num) from t_emp_salary e_s where e_s.emp_salary_date like '" + date 
					+ "' and e_s.emp_salary_state = '已发'"
					+ " and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_name = '基本工资')"
					+ " and e_s.emp_id in (select emp_id from t_emp where dep_id in (" + allDeps 
					+ "))";
			// 获取查询结果
			String depMinBaseSalary = (String) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (depMinBaseSalary == null) {
				depMinBaseSalary = "0";
			}
			// 返回查询结果
			return Integer.parseInt(depMinBaseSalary);
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	/* 获取某 [年度] 某 [月度] 某[部门](含子部门)的 最高基本工资 */
	public Integer getDepMaxBaseSalary(Integer year, Integer month, String allDeps) throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-";
			if (month != null) {
				if (month < 10) {
					date += "0" + month;
				}
				else {
					date += month;
				}
			}
			else {
				date += "%";
			}
			// 声明SQL语句
			String sql = "select max(e_s.emp_salary_num) from t_emp_salary e_s where e_s.emp_salary_date like '" + date 
					+ "' and e_s.emp_salary_state = '已发'"
					+ " and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_name = '基本工资')"
					+ " and e_s.emp_id in (select emp_id from t_emp where dep_id in (" + allDeps 
					+ "))";
			// 获取查询结果
			String depMaxBaseSalary = (String) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (depMaxBaseSalary == null) {
				depMaxBaseSalary = "0";
			}
			// 返回查询结果
			return Integer.parseInt(depMaxBaseSalary);
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}

	/* 获取某 [年度] 某[员工] 的 总基本工资 */
	@Override
	public Integer getEmpTotalBaseSalary(Integer year, Integer empId) throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-%";
			// 声明SQL语句
			String sql = "select SUM(e_s.emp_salary_num) from t_emp_salary e_s where e_s.emp_salary_date like '" + date 
					+ "' and e_s.emp_salary_state = '已发'"
					+ " and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_name = '基本工资')"
					+ " and e_s.emp_id = " + empId;
			// 获取查询结果
			Double empTotalBaseSalary = (Double) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (empTotalBaseSalary == null) {
				empTotalBaseSalary = 0.0;
			}
			// 返回查询结果
			return empTotalBaseSalary.intValue();
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	/* 获取某 [年度] 某[员工] 的 总养老保险 */
	@Override
	public Integer getEmpTotalPension(Integer year, Integer empId) throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-%";
			// 声明SQL语句
			String sql = "select SUM(e_s.emp_salary_num) from t_emp_salary e_s where e_s.emp_salary_date like '" + date 
					+ "' and e_s.emp_salary_state = '已发'"
					+ " and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_name LIKE '%养老保险')"
					+ " and e_s.emp_id = " + empId;
			// 获取查询结果
			Double empTotalPension = (Double) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (empTotalPension == null) {
				empTotalPension = 0.0;
			}
			// 返回查询结果
			return empTotalPension.intValue();
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	/* 获取某 [年度] 某[员工] 的 总医疗保险 */
	@Override
	public Integer getEmpTotalMedical(Integer year, Integer empId) throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-%";
			// 声明SQL语句
			String sql = "select SUM(e_s.emp_salary_num) from t_emp_salary e_s where e_s.emp_salary_date like '" + date 
					+ "' and e_s.emp_salary_state = '已发'"
					+ " and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_name LIKE '%医疗保险')"
					+ " and e_s.emp_id = " + empId;
			// 获取查询结果
			Double empTotalMedical = (Double) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (empTotalMedical == null) {
				empTotalMedical = 0.0;
			}
			// 返回查询结果
			return empTotalMedical.intValue();
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	/* 获取某 [年度] 某[员工] 的 总公积金 */
	@Override
	public Integer getEmpTotalHousingFund(Integer year, Integer empId) throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-%";
			// 声明SQL语句
			String sql = "select SUM(e_s.emp_salary_num) from t_emp_salary e_s where e_s.emp_salary_date like '" + date 
					+ "' and e_s.emp_salary_state = '已发'"
					+ " and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_name LIKE '%公积金')"
					+ " and e_s.emp_id = " + empId;
			// 获取查询结果
			Double empTotalHousingFund = (Double) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (empTotalHousingFund == null) {
				empTotalHousingFund = 0.0;
			}
			// 返回查询结果
			return empTotalHousingFund.intValue();
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	/* 获取某 [年度] 某[员工] 的 总病假扣款 */
	@Override
	public Integer getEmpTotalSickleaveHold(Integer year, Integer empId)	throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-%";
			// 声明SQL语句
			String sql = "select SUM(e_s.emp_salary_num) from t_emp_salary e_s where e_s.emp_salary_date like '" + date 
					+ "' and e_s.emp_salary_state = '已发'"
					+ " and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_name LIKE '病假扣款')"
					+ " and e_s.emp_id = " + empId;
			// 获取查询结果
			Double empTotalSickleaveHold = (Double) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (empTotalSickleaveHold == null) {
				empTotalSickleaveHold = 0.0;
			}
			// 返回查询结果
			return empTotalSickleaveHold.intValue();
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	/* 获取某 [年度] 某[员工] 的 总加班工资 */
	@Override
	public Integer getEmpTotalOverworkSalary(Integer year, Integer empId) throws SmsDaoException {
		try {
			// 处理日期
			String date = year + "-%";
			// 声明SQL语句
			String sql = "select SUM(e_s.emp_salary_num) from t_emp_salary e_s where e_s.emp_salary_date like '" + date 
					+ "' and e_s.emp_salary_state = '已发'"
					+ " and e_s.salary_item_id in (SELECT s.salary_item_id from t_salary_item s WHERE s.salary_item_name LIKE '加班工资')"
					+ " and e_s.emp_id = " + empId;
			// 获取查询结果
			Double empTotalOverworkSalary = (Double) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			if (empTotalOverworkSalary == null) {
				empTotalOverworkSalary = 0.0;
			}
			// 返回查询结果
			return empTotalOverworkSalary.intValue();
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}

	/* 根据 [员工工资项]id 获取 [工资项]名称 */
	public String getSalaryItemNameByEmpSalaryId(Integer esId) throws SmsDaoException {
		try {
			// 声明SQL语句
			String sql = "select salary_item_name from t_salary_item where salary_item_id = (select salary_item_id from t_emp_salary where emp_salary_id = " + esId + ")";
			// 执行更新操作
			String name = (String) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			// 返回查询结果
			return name;
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	
	/* 根据 [员工工资项]id 获取 [工资项]显示顺序 */
	public Integer getSalaryItemOrderByEmpSalaryId(Integer esId) throws SmsDaoException {
		try {
			// 声明SQL语句
			String sql = "select salary_order from t_salary_item where salary_item_id = (select salary_item_id from t_emp_salary where emp_salary_id = " + esId + ")";
			// 执行更新操作
			Integer order = (Integer) sqlQueryBaseDao.queryBySql(sql, null, null, false, false);
			// 返回查询结果
			return order;
		} catch (Exception e) {
			e.printStackTrace();
			throw new SmsDaoException();
		}
	}
}